﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data.SqlClient;
using System.Data;

namespace BLL
{
    public class XETNGHIEMBLL
    {
        DataService DB = new DataService();

        public void insert(int mabenhan,string tenxetnghiem, DateTime ngaytao, string file)
        {
            string sql = "insert into xetnghiem values (@mabenhan,@tenxetnghiem,@ngaytao,@file)";

            SqlParameter paraMaba = new SqlParameter("mabenhan", mabenhan);
            SqlParameter paraTen = new SqlParameter("tenxetnghiem", tenxetnghiem);
            SqlParameter paraNgaytao = new SqlParameter("ngaytao", ngaytao);
            SqlParameter paraFile = new SqlParameter("file", file);

            DB.ExecuteNonQuery(sql, paraMaba, paraTen, paraNgaytao, paraFile);
        }

        public void update(int maxetnghiem, int mabenhan, string tenxetnghiem, DateTime ngaytao, string file_patch)
        {
            string sql = "update xetnghiem set mabenhan = '" + mabenhan.ToString() + "', tenxetnghiem = '" + tenxetnghiem + "', ngaytao = '" + ngaytao.ToString() + "' , file_patch = " + file_patch + " where maxetnghiem = " + maxetnghiem.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int maxetnghiem)
        {
            string sql = "delete from danhmuc where maxetnghiem = " + maxetnghiem.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public XETNGHIEM getBestXetnghiem() {
            string sql = "select top 1  * from xetnghiem order by maxetnghiem desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            XETNGHIEM xn = new XETNGHIEM();
            xn.maxetnghiem = Int32.Parse(r[0].ToString());
            xn.mabenhan = Int32.Parse(r[1].ToString());
            xn.tenxetnghiem = r[2].ToString();
            xn.ngaytao = Convert.ToDateTime(r[3].ToString());
            try
            {
                xn.file_patch = r[4].ToString();
            }
            catch {
                xn.file_patch = "";
            }


            return xn;
        }

        public List<XETNGHIEM> getXetnghiemWithIdBaiviet(int idBaiviet) {
            string sql = "select xn.maxetnghiem, xn.mabenhan ,xn.tenxetnghiem, xn.ngaytao, xn.file_patch "
                        + " from xetnghiem xn,hosobenhan hsba,baiviet bv where hsba.mabenhan = xn.mabenhan "
                        + " and bv.mabaiviet = hsba.mabaiviet and bv.mabaiviet = " + idBaiviet.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<XETNGHIEM> listXn = new List<XETNGHIEM>();
            foreach(DataRow r in table.Rows){
                XETNGHIEM xn = new XETNGHIEM();
                xn.maxetnghiem = Int32.Parse(r[0].ToString());
                xn.mabenhan = Int32.Parse(r[1].ToString());
                xn.tenxetnghiem = r[2].ToString();
                xn.ngaytao = Convert.ToDateTime(r[3].ToString());
                try
                {
                    xn.file_patch = r[4].ToString();
                }
                catch
                {
                    xn.file_patch = "";
                }

                listXn.Add(xn);
            }

            return listXn;
        }

        public List<XNDataShow> getDanhmucxetnghiemChossedWithIDXetnghiem(int idXetnghiem) {
            string sql = "select ctdm.ketqua, dm.tendanhmuc from xetnghiem xn, chitiet_danhmuc ctdm, danhmuc dm "
                        + " where xn.maxetnghiem = ctdm.maxetnghiem and dm.madanhmuc = ctdm.madanhmuc and xn.maxetnghiem = " + idXetnghiem.ToString();

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<XNDataShow> listXnShow = new List<XNDataShow>();
            foreach(DataRow r in table.Rows){

                XNDataShow xnShow = new XNDataShow();
                xnShow.ketqua = r[0].ToString();
                xnShow.tendanhmuc = r[1].ToString();

                listXnShow.Add(xnShow);
            }

            return listXnShow;
        }
    }
}
